package edu.dali.enterprise_test.kits;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class PoiUtils {

    public List<Map<String, String>> readStoreExcelByFs(InputStream is, String type) {
        List<Map<String, String>> mDatas = new ArrayList<Map<String, String>>();
        try {
            Workbook wb = WorkbookFactory.create(is);

            Sheet sheet4Head = wb.getSheet("汇总");
            Row headRow = sheet4Head.getRow(1);
            int colums = sheet4Head.getRow(1).getLastCellNum();
            //读取头信息
            Cell headerCell = null;
            List<String> header = new ArrayList<String>();
            List<Integer> headerIndex = new ArrayList<Integer>();
            FormulaEvaluator evaluator = null;
            if (type.equals("xls"))
                evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
            else if (type.equals("xlsx"))
                evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
            for (int i = 0; i < colums; i++) {
                headerCell = headRow.getCell(i);
                String value = "";
                try {
                    value = getCellString(evaluator, headerCell, value);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                if (i == 0 && value.isEmpty())
                    break;
                header.add(value);
                headerIndex.add(i);
            }


            int sheetCount = wb.getNumberOfSheets();

            Sheet sheet = wb.getSheet("汇总");
            Row row;
            List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
            ArrayList<String> rowData;
            boolean flag = false;
            for (int i = 2; i < sheet.getLastRowNum() + 1; i++) {
                if (flag) break;
                rowData = new ArrayList<String>();
                row = sheet.getRow(i);
                for (int j = 0; j < colums; j++) {
                    String rowValue = "";
                    try {
                        rowValue = getCellString(evaluator, row.getCell(j), rowValue);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    if (j == 0 && rowValue.isEmpty()) {
                        break;
                    }
                    if (j == 0 && rowValue.replaceAll("\\s*", "").equals("合计")) {
                        flag = true;
                        break;
                    }
                    rowData.add(rowValue);
                }
                if (rowData.size() > 0)
                    list.add(rowData);
            }
            Map<Integer, String> datas = new HashMap<Integer, String>();
            for (int i = 0; i < header.size(); i++) {
                int index = header.indexOf(header.get(i));
                int ii = headerIndex.get(index);
                datas.put(ii, header.get(i));
            }


            for (int i = 0; i < list.size(); i++) {
                Map<String, String> mRowData = new LinkedHashMap<String, String>();
                for (Map.Entry<Integer, String> entry : datas.entrySet()) {
                    if (!mRowData.containsKey(entry.getKey())) {
                        mRowData.put(entry.getValue(), list.get(i).get(entry.getKey()));
                    }
                }
                mDatas.add(mRowData);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return mDatas;
    }

    /**
     * 读取excel
     *
     * @param is
     * @param type
     */
    public List<Map<String, String>> readExcelByFs(InputStream is, String type) {
        List<Map<String, String>> mDatas = new ArrayList<Map<String, String>>();
        try {
            Workbook wb = WorkbookFactory.create(is);

            Sheet sheet4Head = wb.getSheet("Sheet1");
            Row headRow = sheet4Head.getRow(0);
            int colums = sheet4Head.getRow(0).getLastCellNum();
            //读取头信息
            Cell headerCell = null;
            List<String> header = new ArrayList<String>();
            List<Integer> headerIndex = new ArrayList<Integer>();
            FormulaEvaluator evaluator = null;
            if (type.equals("xls"))
                evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
            else if (type.equals("xlsx"))
                evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
            for (int i = 0; i < colums; i++) {
                headerCell = headRow.getCell(i);
                String value = "";
                try {
                    value = getCellString(evaluator, headerCell, value);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                if (i == 0 && value.isEmpty())
                    break;
                header.add(value);
                headerIndex.add(i);
            }


            int sheetCount = wb.getNumberOfSheets();
            //得到所有的sheet 然后读取
//            for (int iindex = 0; iindex < sheetCount; iindex++) {
                Sheet sheet = wb.getSheet("Sheet1");
                Row row;
                List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
                ArrayList<String> rowData;
                boolean flag = false;
                for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                    if (flag) break;
                    rowData = new ArrayList<String>();
                    row = sheet.getRow(i);
                    for (int j = 0; j < colums; j++) {
                        String rowValue = "";
                        try {
                            rowValue = getCellString(evaluator, row.getCell(j), rowValue);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        if (j == 0 && rowValue.isEmpty()) {
                            break;
                        }
                        if (j == 0 && rowValue.replaceAll("\\s*", "").equals("合计")) {
                            flag = true;
                            break;
                        }
                        rowData.add(rowValue);
                    }
                    if (rowData.size() > 0)
                        list.add(rowData);
                }
                Map<Integer, String> datas = new HashMap<Integer, String>();
                for (int i = 0; i < header.size(); i++) {
                    int index = header.indexOf(header.get(i));
                    int ii = headerIndex.get(index);
                    datas.put(ii, header.get(i));
                }


                for (int i = 0; i < list.size(); i++) {
                    Map<String, String> mRowData = new LinkedHashMap<String, String>();
                    for (Map.Entry<Integer, String> entry : datas.entrySet()) {
                        if (!mRowData.containsKey(entry.getKey())) {
                            mRowData.put(entry.getValue(), list.get(i).get(entry.getKey()));
                        }
                    }
                    mDatas.add(mRowData);
                }
//            }


        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return mDatas;
    }

    /**
     * 读取字段用的
     *
     * @param evaluator
     * @param mCell
     * @param value
     * @return
     */
    private String getCellString(FormulaEvaluator evaluator, Cell mCell, String value) {
        if (mCell == null) return "";
        switch (mCell.getCellTypeEnum()) {
            case BLANK:
                value = "";
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(mCell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                    value = sdf.format(DateUtil.getJavaDate(mCell.getNumericCellValue()));
                } else {
//                    value = Double.toString(mCell.getNumericCellValue());
                    DecimalFormat df = new DecimalFormat("0");
                    value = df.format(mCell.getNumericCellValue());
                }
                break;
            case STRING:
                value = mCell.getStringCellValue();
                break;
            case FORMULA:
                value = Double.toString(evaluator.evaluateInCell(mCell).getNumericCellValue());
                break;
            default:
                value = "";
                break;
        }
        return value;
    }
}
